/*
	
	This Proc is Used for to close the Drop Out Lots
	exec SP_CLOSE_ALL_DROP_OUT_LOTS 'Chipbond'

*/
ALTER PROC SP_CLOSE_ALL_DROP_OUT_LOTS( 
	@VENDOR_NAME VARCHAR(50)
)
AS
BEGIN
	DECLARE @PART_NUMBER VARCHAR(50)
	DECLARE @lot_number VARCHAR(50)
	DECLARE @fab_Lot_Number VARCHAR(50)
	DECLARE @ps_type VARCHAR(50)
	DECLARE @wafer_qty_in INT
	DECLARE @qty_in       INT
	/*Inserting Wip Drop Out Lots in Drop Out Table */
	INSERT INTO DROP_OUT_LOT_TABLE(LOT_ID, DROP_TYPE, [TIMESTAMP]) 
		( SELECT LOT_ID, 'WIP', CURRENT_TIMESTAMP FROM LOT_TABLE WHERE LOCATION_ID IN 
		( SELECT LOCATION_ID FROM LOCATION WHERE L_VENDOR_NAME = @VENDOR_NAME ) AND 
		LOT_DROP_OUT = 1 AND LOT_ENTERED_BY = 1 AND  LOT_CURRENT_STATUS != 'Close')
	/* For Drop out Lot Alert */
	declare  @wipDropLotsCursor cursor
	SET @wipDropLotsCursor = CURSOR FOR SELECT PART_NUMBER, LOT_NUMBER,FAB_LOT_NUMBER, isNUll(Qty_In,Wafer_qty_In), PS_TYPE  FROM PRODUCT P, PROCESS_STEP PS, LOT_TABLE LT WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID
	AND LT.LOT_ID IN ( SELECT LOT_ID FROM LOT_TABLE WHERE LOCATION_ID IN ( SELECT LOCATION_ID FROM LOCATION WHERE L_VENDOR_NAME = @VENDOR_NAME ) AND 
	LOT_DROP_OUT = 1 AND LOT_ENTERED_BY = 1 AND  LOT_CURRENT_STATUS != 'Close')

	OPEN @wipDropLotsCursor
	FETCH NEXT FROM @wipDropLotsCursor INTO @PART_NUMBER, @lot_number,@fab_Lot_Number,@qty_in, @ps_type
	WHILE ( @@FETCH_STATUS = 0 )
	BEGIN
		
		IF ( SELECT COUNT(*) FROM NOTIFICATION_ALERT_TABLE WHERE PART_NUMBER = @PART_NUMBER AND LOT_NUMBER = @LOT_NUMBER AND FAB_LOT_NUMBER = @fab_Lot_Number AND Notification_Name = 'DropOut Lot Notification' AND VENDOR_NAME = @VENDOR_NAME AND PROCESS_TYPE = @ps_type AND IS_USER_NOTIFIED = 1 ) = 0 
		BEGIN
			INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Die_Qty, Status,TimeStamp, Inventory_Type)
				VALUES ( @part_number, @lot_number, @fab_Lot_Number, @vendor_name, @ps_type,'DropOut Lot Notification', @qty_in,'Open', CURRENT_TIMESTAMP, 'WIP' )
		END

		SET @PART_NUMBER = null	
		SET @lot_number = null
		SET @fab_Lot_Number = null
		SET @qty_in			= null
		SET @ps_type = null
	FETCH NEXT FROM @wipDropLotsCursor INTO @PART_NUMBER, @lot_number,@fab_Lot_Number,@qty_in, @ps_type
	END
	CLOSE @wipDropLotsCursor
	DEALLOCATE @wipDropLotsCursor

	/*Closing the Wip Drop Out Lots*/
	UPDATE LOT_TABLE SET LOT_CURRENT_STATUS = 'Close' WHERE LOT_ID IN 
		( SELECT Lot_Id FROM Lot_Table Where LOCATION_ID in ( select Location_id from LOCATION WHERE L_VENDOR_NAME = @VENDOR_NAME ) AND 
		LOT_DROP_OUT = 1 AND LOT_ENTERED_BY = 1 AND LOT_CURRENT_STATUS != 'Close')

	/*Inserting Transit Drop Out Lots in Drop Out Table*/
	INSERT INTO DROP_OUT_LOT_TABLE(LOT_ID, DROP_TYPE, [TIMESTAMP])
		( SELECT SL.LOT_ID, 'SHIP', CURRENT_TIMESTAMP FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE LT.LOCATION_ID IN 
		( SELECT LOCATION_ID FROM LOCATION WHERE L_VENDOR_NAME = @VENDOR_NAME )
		AND LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'TRANSIT' AND SL.SHIP_STATUS = 0 and Ship_Drop_out = 1 AND LT.LOT_ENTERED_BY = 1 )


	/* For Drop out Lot Alert */
	declare  @shipDropLotsCursor cursor
	SET @shipDropLotsCursor = CURSOR FOR SELECT PART_NUMBER, LOT_NUMBER,FAB_LOT_NUMBER, ship_Qty, PS_TYPE  FROM PRODUCT P, PROCESS_STEP PS, LOT_TABLE LT, Shipped_Lot SL WHERE P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID 
	AND PS.PROCESSSTEP_ID = LT.PROCESSSTEP_ID AND LT.LOT_ID = SL.LOT_ID
	AND LT.LOT_ID IN ( SELECT SL.LOT_ID FROM SHIPPED_LOT SL, LOT_TABLE LT WHERE LT.LOCATION_ID IN 
		( SELECT LOCATION_ID FROM LOCATION WHERE L_VENDOR_NAME = @VENDOR_NAME )
		AND LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'TRANSIT' AND SL.SHIP_STATUS = 0 and Ship_Drop_out = 1 AND LT.LOT_ENTERED_BY = 1 )

	OPEN @shipDropLotsCursor
	FETCH NEXT FROM @shipDropLotsCursor INTO @PART_NUMBER, @lot_number,@fab_Lot_Number,@qty_in, @ps_type
	WHILE ( @@FETCH_STATUS = 0 )
	BEGIN
		
		IF ( SELECT COUNT(*) FROM NOTIFICATION_ALERT_TABLE WHERE PART_NUMBER = @PART_NUMBER AND LOT_NUMBER = @LOT_NUMBER AND FAB_LOT_NUMBER = @fab_Lot_Number AND Notification_Name = 'DropOut Lot Notification' AND VENDOR_NAME = @VENDOR_NAME AND PROCESS_TYPE = @ps_type AND IS_USER_NOTIFIED = 1 ) = 0 
		BEGIN
			INSERT INTO Notification_Alert_Table ( Part_Number, Lot_Number, Fab_Lot_Number, Vendor_Name, Process_Type, Notification_Name, Die_Qty, Status,TimeStamp, Inventory_Type)
				VALUES ( @part_number, @lot_number, @fab_Lot_Number, @vendor_name, @ps_type,'DropOut Lot Notification', @qty_in,'Open', CURRENT_TIMESTAMP, 'SHIP' )
		END

		SET @PART_NUMBER = null	
		SET @lot_number = null
		SET @fab_Lot_Number = null
		SET @qty_in			= null
		SET @ps_type = null
	FETCH NEXT FROM @shipDropLotsCursor INTO @PART_NUMBER, @lot_number,@fab_Lot_Number,@qty_in, @ps_type
	END
	CLOSE @shipDropLotsCursor
	DEALLOCATE @shipDropLotsCursor

	/*Closing the Transit Drop Out Lots*/
	UPDATE Shipped_Lot SET STATUS = 'Close', Ship_Status = 1 WHERE Lot_Id in ( SELECT SL.LOT_ID FROM SHIPPED_LOT SL, 
	LOT_TABLE LT WHERE LT.LOCATION_ID IN ( SELECT LOCATION_ID FROM LOCATION WHERE L_VENDOR_NAME = @VENDOR_NAME )
	AND LT.LOT_ID = SL.LOT_ID AND SL.STATUS = 'TRANSIT' AND SL.SHIP_STATUS = 0 and Ship_Drop_out = 1 AND LT.LOT_ENTERED_BY = 1 )
END